unit frmDTS;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, RzTabs, siComp, Mask, RzEdit, RzBtnEdt, ZAbstractRODataset,
  ZAbstractDataset, ZDataset, DB, ADODB, ZConnection, AdoConEd2, RzButton, CheckLst, SynEdit,
  SynMemo, SynEditor, ZAbstractTable, cxEdit, cxDBData,
  cxGridLevel, cxClasses, cxControls, cxGridCustomView, SQLiteUtils, untDTSFmt,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid,
  RaGSQLite, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage;

type

  TDriverType = (dtADO, dtZEOS);

  TFormDTS = class(TForm)
    siLang:       TsiLang;
    ZConn:        TZConnection;
    AConn:        TADOConnection;
    AQry:         TADOQuery;
    ZQry:         TZQuery;
    ppDTS:        TRzPageControl;
    ts1:          TRzTabSheet;
    GroupBox1:    TGroupBox;
    lblTblName:   TLabel;
    GroupBox2:    TGroupBox;
    RadioGroup1:  TRadioGroup;
    ppDriver:     TRzPageControl;
    tsADO:        TRzTabSheet;
    Label2:       TLabel;
    btnConn:      TButton;
    edtConnStr:   TRzButtonEdit;
    tsZeos:       TRzTabSheet;
    tsMap:        TRzTabSheet;
    tsImport:     TRzTabSheet;
    btnPrev:      TButton;
    btnNext:      TButton;
    odDB:         TOpenDialog;
    ScrollBox1:   TScrollBox;
    lblDb:        TLabel;
    Label3:       TLabel;
    Label4:       TLabel;
    Label5:       TLabel;
    Label6:       TLabel;
    Label7:       TLabel;
    Label8:       TLabel;
    Protocol:     TLabel;
    cbDriver:     TComboBox;
    edtZDB:       TRzButtonEdit;
    edtHost:      TEdit;
    edtPort:      TEdit;
    edtCatelog:   TEdit;
    edtUser:      TEdit;
    edtPwd:       TEdit;
    mmProp:       TMemo;
    btnZConn:     TButton;
    btnOpenDTS:   TRzBitBtn;
    btnSAveDTS:   TRzBitBtn;
    mmDTSSQL:     TSynEditor;
    Label1:       TLabel;
    lstFields:    TCheckListBox;
    Label9:       TLabel;
    btnTestSQL:   TButton;
    Label11:      TLabel;
    cbSourceTbl:  TComboBox;
    lstSourceFld: TListBox;
    RzBitBtn1:    TRzBitBtn;
    ATbl:         TADOTable;
    ZTbl:         TZTable;
    Label10:      TLabel;
    lstImportFD:  TListBox;
    Label12:      TLabel;
    gdImportDBTv: TcxGridDBTableView;
    gdImportLv:   TcxGridLevel;
    gdImport:     TcxGrid;
    Button1:      TButton;
    dsImport:     TDataSource;
    odDTS: TOpenDialog;
    sdDTS: TSaveDialog;
    ZOrgTable: TRaSQLiteTable;
    procedure edtConnStrButtonClick(Sender: TObject);
    procedure btnConnClick(Sender: TObject);
    procedure edtZDBButtonClick(Sender: TObject);
    procedure RadioGroup1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnZConnClick(Sender: TObject);
    procedure ppDTSChange(Sender: TObject);
    procedure btnPrevClick(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnTestSQLClick(Sender: TObject);
    procedure RzBitBtn1Click(Sender: TObject);
    procedure cbSourceTblChange(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure btnOpenDTSClick(Sender: TObject);
    procedure btnSAveDTSClick(Sender: TObject);
  private
    FTableName:  string;
    FDriverType: TDriverType;
    procedure SetTableName(const Value: string);
    { Private declarations }
  public
    property DriverType: TDriverType Read FDriverType Write FDriverType;
    property TableName: string Read FTableName Write SetTableName;
  end;

var
  FormDTS: TFormDTS;

implementation

uses
  frmMain;

{$R *.dfm}

procedure TFormDTS.btnConnClick(Sender: TObject);
begin
  try
    AConn.Close;
    AConn.ConnectionString := edtConnStr.Text;
    AConn.Open;
    cbSourceTbl.Items.Clear;
    AConn.GetTableNames(cbSourceTbl.Items);
    lstSourceFld.Items.Clear;
    btnNext.Enabled := True;
  except
    on E: Exception do
      MessageBox(handle, PChar(E.Message), PChar(FormMain.siLang.GetText('sHint')), MB_OK or MB_ICONINFORMATION);
  end;
end;

procedure TFormDTS.btnNextClick(Sender: TObject);
begin
  ppDTS.ActivePageIndex := ppDTS.ActivePageIndex + 1;
  ppDTSChange(nil);
end;

procedure TFormDTS.btnOpenDTSClick(Sender: TObject);
begin
  //  open DTS
  if not odDTS.Execute() then
    Exit;
  with TDTSFmt.Create do
  begin
    LoadFromFile(odDTS.FileName);
    self.TableName := TableName;
    RadioGroup1.ItemIndex := DriverType;
    RadioGroup1Click(nil);
    edtConnStr.Text := ConnectionString;
    cbDriver.ItemIndex := cbDriver.Items.IndexOf(Protocol);
    edtZDB.Text := Database;
    edtHost.Text := HostName;
    edtPort.Text := IntToStr(Port);
    edtCatelog.Text := Catelog;
    edtUser.Text := UserName;
    edtPwd.Text := Password;
    mmProp.Lines.Text := Properties;
    mmDTSSQL.Lines.Text := DTSSQL;
    StrToField(lstFields, DestFields);
    Free;
  end;  
end;

procedure TFormDTS.btnPrevClick(Sender: TObject);
begin
  ppDTS.ActivePageIndex := ppDTS.ActivePageIndex - 1;
  ppDTSChange(nil);
end;

procedure TFormDTS.btnSAveDTSClick(Sender: TObject);
begin
  //  save DTS
  if not sdDTS.Execute() then
    Exit;
  with TDTSFmt.Create do
  begin
    TableName := self.TableName;
    DriverType := RadioGroup1.ItemIndex;
    ConnectionString := edtConnStr.Text;
    Protocol := cbDriver.Text;
    Database := edtZDB.Text;
    HostName := edtHost.Text;
    Port := StrToIntDef(edtPort.Text,0);
    Catelog := edtCatelog.Text;
    UserName := edtUser.Text;
    Password := edtPwd.Text;
    Properties := mmProp.Lines.Text;
    DTSSQL := mmDTSSQL.Lines.Text;
    FieldToStr(lstFields);
    SaveToFile(sdDTS.FileName);
    Free;
  end;
end;

procedure TFormDTS.btnTestSQLClick(Sender: TObject);
begin
  //  test sql
  case DriverType of
    dtADO:
    begin
      AQry.Close;
      AQry.SQL.Text := LowerCase(mmDTSSQL.Lines.Text);
      try
        AQry.Open;
        MessageBox(Handle, PChar(siLang.GetText('sTestSQLOK')), PChar(formmain.siLang.GetText('sHint')), MB_OK or MB_ICONINFORMATION);
      except
        on E: Exception do
          MessageBox(Handle, PChar(format(siLang.GetText('sTestSQLFail'), [E.message])), PChar(formmain.siLang.GetText('sError')), MB_OK or MB_ICONERROR);
      end;

    end;
    dtZEOS:
    begin
      ZQry.Close;
      ZQry.SQL.Text := LowerCase(mmDTSSQL.Lines.Text);
      try
        ZQry.Open;
        MessageBox(Handle, PChar(siLang.GetText('sTestSQLOK')), PChar(formmain.siLang.GetText('sHint')), MB_OK or MB_ICONINFORMATION);
      except
        on E: Exception do
          MessageBox(Handle, PChar(format(siLang.GetText('sTestSQLFail'), [E.message])), PChar(formmain.siLang.GetText('sError')), MB_OK or MB_ICONERROR);
      end;

    end;
  end;
end;

procedure TFormDTS.btnZConnClick(Sender: TObject);
begin
  btnNext.Enabled := False;
  ZConn.Disconnect;
  ZConn.Protocol        := cbDriver.Text;
  ZConn.Database        := edtZDB.Text;
  ZConn.HostName        := edtHost.Text;
  zconn.Port            := StrToIntDef(edtPort.Text, 0);
  ZConn.Catalog         := edtCatelog.Text;
  ZConn.User            := edtUser.Text;
  ZConn.Password        := edtPwd.Text;
  ZConn.Properties.Text := mmProp.Lines.Text;
  try
    ZConn.Connect;
    cbSourceTbl.Items.Clear;
    ZConn.GetTableNames(EmptyStr, cbSourceTbl.Items);
    lstSourceFld.Items.Clear;
    btnNext.Enabled := True;
  except
    on E: Exception do
      MessageBox(handle, PChar(E.Message), PChar(FormMain.siLang.GetText('sHint')), MB_OK or MB_ICONINFORMATION);
  end;
end;

procedure TFormDTS.Button1Click(Sender: TObject);
var
  i: Integer;
begin
  //  do import data
  case DriverType of
    dtADO:
    begin
      ZOrgTable.Connection.StartTransaction;
      AQry.First;
      while not AQry.Eof do
      begin
        ZOrgTable.Append;
        for i := 0 to AQry.FieldCount - 1 do
         ZOrgTable.FieldByName(GetFieldNameInList(lstImportFD.Items[i])).Value :=
          AQry.FieldByName(GetFieldNameInList(lstImportFD.Items[i])).Value;
        try
          ZOrgTable.Post;
        except
          on E: Exception do
          begin
            ZOrgTable.Connection.RollBack;
            MessageBox(Handle,PChar(E.Message),PChar(FormMain.siLang.GetText('sError')),MB_OK or MB_ICONINFORMATION);
            Exit;
          end;
        end;
        AQry.Next;
      end;
      ZOrgTable.Connection.Commit;
    end;
    dtZEOS:
    begin
      ZOrgTable.Connection.StartTransaction;
      ZQry.First;
      while not ZQry.Eof do
      begin
        ZOrgTable.Append;
        for i := 0 to ZQry.FieldCount - 1 do
         ZOrgTable.FieldByName(GetFieldNameInList(lstImportFD.Items[i])).Value :=
          ZQry.FieldByName(GetFieldNameInList(lstImportFD.Items[i])).Value;
        try
          ZOrgTable.Post;
        except
          on E: Exception do
          begin
            ZOrgTable.Connection.RollBack;
            MessageBox(Handle,PChar(E.Message),PChar(FormMain.siLang.GetText('sError')),MB_OK or MB_ICONINFORMATION);
            Exit;
          end;
        end;
        ZQry.Next;
      end;
      ZOrgTable.Connection.Commit;
    end;
  end;
  MessageBox(handle,PChar(siLang.GetText('sImportOk')),PChar(formmain.siLang.GetText('sHint')), MB_OK or MB_ICONINFORMATION);
end;

procedure TFormDTS.cbSourceTblChange(Sender: TObject);
var
  fd: TField;
  i: Integer;
begin
  case DriverType of
    dtADO:
    begin
      ATbl.Close;
      ATbl.TableName := cbSourceTbl.Text;
      ATbl.Open;
      lstSourceFld.Items.Clear;
      for i:=0 to ATbl.Fields.Count -1 do
      begin
        fd :=ATbl.Fields[i];
        lstSourceFld.Items.Add(fd.FieldName);
      end;  
//      for fd in ATbl.Fields do
//        lstSourceFld.Items.Add(fd.FieldName);
    end;
    dtZEOS:
    begin
      ZTbl.Close;
      ZTbl.TableName := cbSourceTbl.Text;
      ZTbl.Open;
      lstSourceFld.Items.Clear;
      for i:= 0 to ZTbl.Fields.Count - 1 do
      begin
        fd := ZTbl.Fields[i];
        lstSourceFld.Items.Add(fd.FieldName);
      end;  
//      for fd in ZTbl.Fields do
//        lstSourceFld.Items.Add(fd.FieldName);
    end;
  end;
end;

procedure TFormDTS.edtConnStrButtonClick(Sender: TObject);
begin
  if EditConnectionString(AConn) then
  begin
    AConn.Close;
    btnNext.Enabled := False;
    edtConnStr.Text := AConn.ConnectionString;
  end;
end;

procedure TFormDTS.edtZDBButtonClick(Sender: TObject);
begin
  if odDB.Execute then
    edtZDB.Text := odDB.FileName;
end;

procedure TFormDTS.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
//  ZOrgConn.Disconnect;
  AConn.Close;
  ZConn.Disconnect;
end;

procedure TFormDTS.FormCreate(Sender: TObject);
begin
  DriverType            := dtADO;
  gdImportDBTv.OptionsView.NoDataToDisplayInfoText := EmptyStr;
  siLang.ActiveLanguage := formmain.Config.Language;

//  ZOrgConn.Disconnect;
//  ZOrgConn.Database := FormMain.Conn.Database;
//  ZOrgConn.Connect;

end;

procedure TFormDTS.ppDTSChange(Sender: TObject);
var
  i: integer;
begin
  case ppDTS.ActivePageIndex of
    0:
    begin
      ts1.TabVisible      := True;
      tsMap.TabVisible    := False;
      tsImport.TabVisible := False;
      ppDTS.ActivePage    := ts1;

      btnPrev.Enabled := False;
      btnNext.Enabled := False;
      case DriverType of
        dtADO: if AConn.Connected then
            btnNext.Enabled := True;
        dtZEOS: if ZConn.Connected then
            btnNext.Enabled := True;
      end;
    end;
    1:
    begin
      ts1.TabVisible      := False;
      tsMap.TabVisible    := True;
      tsImport.TabVisible := False;
      ppDTS.ActivePage    := tsMap;

      btnPrev.Enabled := True;
      btnNext.Enabled := True;
    end;
    2:
    begin
      ts1.TabVisible      := False;
      tsMap.TabVisible    := False;
      tsImport.TabVisible := True;
      ppDTS.ActivePage    := tsImport;

      btnPrev.Enabled := True;
      btnNext.Enabled := False;

      lstImportFD.Items.Clear;
      for i := 0 to lstFields.Items.Count - 1 do
      begin
        if lstFields.Checked[i] then
          lstImportFD.Items.Add(lstFields.Items[i]);
      end;

      case DriverType of
        dtADO:
        begin
          AQry.Close;
          AQry.SQL.Text := LowerCase(mmDTSSQL.Lines.Text);
          try
            AQry.Open;
            dsImport.DataSet := AQry;
            (gdImportDBTv.DataController as IcxCustomGridDataController).DeleteAllItems;
            gdImportDBTv.DataController.CreateAllItems;
            for i := 0 to gdImportDBTv.ColumnCount - 1 do
              gdImportDBTv.Columns[i].Options.ShowEditButtons := isebNever;
          except
            on E: Exception do
            begin
              MessageBox(Handle, PChar(format(siLang.GetText('sTestSQLFail'), [E.message])), PChar(formmain.siLang.GetText('sError')), MB_OK or MB_ICONERROR);
              btnPrev.Click;
            end;
          end;
        end;
        dtZEOS:
        begin
          ZQry.Close;
          ZQry.SQL.Text := LowerCase(mmDTSSQL.Lines.Text);
          try
            ZQry.Open;
            dsImport.DataSet := ZQry;
            (gdImportDBTv.DataController as IcxCustomGridDataController).DeleteAllItems;
            gdImportDBTv.DataController.CreateAllItems;
            for i := 0 to gdImportDBTv.ColumnCount - 1 do
              gdImportDBTv.Columns[i].Options.ShowEditButtons := isebNever;
          except
            on E: Exception do
            begin
              MessageBox(Handle, PChar(format(siLang.GetText('sTestSQLFail'), [E.message])), PChar(formmain.siLang.GetText('sError')), MB_OK or MB_ICONERROR);
              btnPrev.Click;
            end;
          end;
        end;
      end;
    end;
  end;
end;

procedure TFormDTS.RadioGroup1Click(Sender: TObject);
begin

  case RadioGroup1.ItemIndex of
    0:
    begin
      tsADO.TabVisible    := True;
      tsZeos.TabVisible   := False;
      ppDriver.ActivePage := tsADO;
      DriverType          := dtADO;
      AConn.Close;
      btnNext.Enabled := False;
    end;
    1:
    begin
      tsADO.TabVisible    := False;
      tsZeos.TabVisible   := True;
      ppDriver.ActivePage := tsZeos;
      DriverType          := dtZEOS;
      ZConn.Disconnect;
      btnNext.Enabled := False;
    end;
  end;
end;

procedure TFormDTS.RzBitBtn1Click(Sender: TObject);
begin
  MessageBox(Handle, PChar(siLang.GetText('sDTSHint')), PChar(formmain.siLang.GetText('sHint')), MB_OK or MB_ICONINFORMATION);
end;

procedure TFormDTS.SetTableName(const Value: string);
var
  DBInfo: TList;
  i:      integer;
begin
  FTableName         := Value;
  lblTblName.Caption := Format(siLang.GetText('sFmtTableName'), [FTableName]);
  lstFields.Items.Clear;

  DBInfo := TList.Create;
  FormMain.Conn.GetTableInfo(FTableName, DBInfo);
  for i := 0 to DBInfo.Count - 1 do
  begin
    with TRaSQLiteField(DBInfo[i]) do
      lstFields.Items.Add(Format('%s (%s)', [FieldName, FieldType]));
  end;
  DBInfo.Free;
  for i := 0 to lstFields.Items.Count - 1 do
    lstFields.Checked[i] := True;

  ZOrgTable.Close;
  ZOrgTable.TableName := FTableName;
  ZOrgTable.Open;
end;

end.
